<?php
/**
 * Created by PhpStorm.
 * User: BlueIdeaLirui
 * Date: 2019/11/11
 * Time: 10:13
 */

namespace business\bigdata\repositories;

use yii\db\Query;

class SupplierRepository
{

    /**
     * @var null|\yii\db\Connection
     */
    protected $dbConnection;

    public function __construct()
    {
        $this->dbConnection = \Yii::$app->get('dbSupplierBigdata');
    }

    /**
     * 获取某个时间后的供应商总数
     * @param $interval string 距今相差天数
     * @return int
     */
    public function getSupplierCountByDate($interval = null)
    {
        $query = (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier');
        if (!empty($interval)) {
            $query->where(['>=', 'update_timestamp', date("Y-m-d", strtotime($interval . " day"))]);
        } else {
            $query->andWhere(['is_enable_by_search' => 0]);
        }
        return (int)$query->count('*', $this->dbConnection);
    }

    /**
     * 获取某个时间后的供应商总数
     * @param $startDate
     * @param $endDate
     * @return int
     * @throws \yii\base\InvalidConfigException
     */
    public function getSupplierCountByTimeSlot($startDate, $endDate)
    {
        return (int)(new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->where(['>=', 'update_timestamp',  $startDate])
            ->andWhere(['<', 'update_timestamp', $endDate])
            ->count('*', $this->dbConnection);
    }

    /**
     * 从大数据表中获取变更的供应商id
     * @param $lastSupplierId
     * @param $pageSize
     * @return array
     */
    public function getAllSupplierId($lastSupplierId, $pageSize)
    {
        return (new Query())->from(['mycommunity_contractor_bigdata.yl_s_supplier'])
            ->select(['id'])
            ->where(['>', 'id', $lastSupplierId])
            ->orderBy(['id' => SORT_ASC])
            ->limit($pageSize)
            ->column($this->dbConnection);
    }

    /**
     * 从大数据表中获取变更的供应商id
     * @param $interval
     * @param $offset
     * @param $pageSize
     * @return array
     */
    public function getChangedSupplierInfo($interval, $offset, $pageSize)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->select(['id'])
            ->where(['>', 'id', $offset])
            ->andWhere(['>=', 'update_timestamp', date("Y-m-d", strtotime($interval . " day"))])
            ->orderBy(['id' => SORT_ASC])
            ->limit($pageSize)
            ->column($this->dbConnection);
    }

    /**
     * 从大数据表中获取变更的供应商id
     * @param $startDate
     * @param $endDate
     * @param $lastSupplierId
     * @param $pageSize
     * @return mixed
     * @throws \yii\base\InvalidConfigException
     */
    public function getChangedSupplierInfoByTimeSlot($startDate, $endDate, $lastSupplierId, $pageSize)
    {
        $result = (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->select(['id'])
            ->where(['>', 'id', $lastSupplierId])
            ->andWhere(['>=', 'update_timestamp', $startDate])
            ->andWhere(['<', 'update_timestamp', $endDate])
            ->orderBy(['id' => SORT_ASC])
            ->limit($pageSize)
            ->column($this->dbConnection);
        return $result;
    }

    public function existInSupplierById($id)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->where(['id' => $id])
            ->exists($this->dbConnection);
    }

    /**
     * 根据供应商id获取供应商基本信息
     * @param array $supplierId
     * @return array|false
     */
    public function getSupplierBasicInfoByIds(array $supplierId)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier as a')
            ->select([
                'a.id', 'a.name', 'a.PROFILE AS profile', 'a.scope', 'a.label', 'a.city', 'a.regist_capi',
                'a.parent_area as province', 'a.three_cate_name_list', 'a.project_score', 'a.business_score',
                'a.three_cate_code_list', 'a.contact_score', 'b.`name` AS city_name', 'a.product_score',
                'a.cooperation_score', 'a.qualification_score', 'a.supervise_score', 'a.update_timestamp',
                'a.num_capi', 'b.parent_name AS province_name'
            ])
            ->leftJoin('mycommunity_contractor_bigdata.yl_dim_area as b', 'a.city =b.code')
            ->where(['a.id' => $supplierId, 'a.is_enable_by_search' => 0, 'is_deleted' => 0])
            ->all($this->dbConnection);
    }

    public function getSupplierNameByIds($supplierId)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->select(['name'])
            ->where(['id' => $supplierId])
            ->scalar($this->dbConnection);
    }

    /**
     * 根据公司名称获取项目案例
     * @param array $supplierNames
     * @return array
     */
    public function getProjectCaseBySupplerNames($supplierNames)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_resolve_project_mohurd')
            ->select(['name', 'project_name', 'source'])
            ->where(['name' => $supplierNames])
            ->andWhere(['<>', 'source', 2])
            ->all($this->dbConnection);
    }

    /**
     * 根据公司名称获取服务资质
     * @param $supplierNames
     * @return array
     */
    public function getQualificationBySupplierNames($supplierNames)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_resolve_qualification_mohurd')
            ->select(['name', 'qualification_name', 'qualification_code', 'level_code'])
            ->where(['name' => $supplierNames])
            ->all($this->dbConnection);
        return $result;
    }

    /**
     * 根据公司ID获取百强供应商标签
     * @param array $supplierIds
     * @return array
     */
    public function getTopDeveloperBySupplerIds($supplierIds)
    {
        return (new Query)->from('mycommunity_contractor_bigdata.yl_s_supplier_top100_developer')
            ->select(['name', 'supplier_id'])
            ->where(['supplier_id' => $supplierIds])
            ->all($this->dbConnection);
    }

    /**
     * 根据供应商id获取top级领域标签
     * @param $supplierIds
     * @return mixed
     */
    public function getTopBusinessAreaSupplierIds($supplierIds)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_top_business_area')
            ->select(['supplier_id', 'business_area'])
            ->where(['supplier_id' => $supplierIds, 'is_deleted' => 0])
            ->all($this->dbConnection);
    }

    /**
     * 根据公司ID获取百强供应商标签
     * @param array $supplierIds
     * @return array
     */
    public function getBoardListBySupplerIds($supplierIds)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_board_list')
            ->select(['name', 'supplier_id', 'url'])
            ->where(['supplier_id' => $supplierIds])
            ->all($this->dbConnection);
    }

    /**
     * 获取合作开发商数量
     * @param $supplierNames
     * @return string|null|false
     */
    public function getCooperationDevelopersNumberById($supplierId)
    {
        $result = (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_cooperation')
            ->select(['count(1) as cnt'])
            ->where(['supplier_id' => $supplierId])
            ->groupBy('developer_name')
            ->all($this->dbConnection);
        return array_sum(array_column($result, 'cnt'));
    }

    /**
     * 根据公司名称获取服务资质
     * @param $supplierName
     * @return array
     */
    public function getQualificationByName($supplierName)
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_resolve_qualification_mohurd')
            ->select(['qualification_name', 'qualification_code', 'level_code'])
            ->where(['name' => $supplierName])
            ->all($this->dbConnection);
    }

    /**
     * 根据公司名称获取专家数量
     * @param $supplierNames
     * @return string|null|false
     */
    public function getMemberNumberByName($supplierName)
    {
        return (int)(new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_resolve_member_mohurd')
            ->where(['name' => $supplierName])
            ->count('*', $this->dbConnection);
    }

    /**
     * 查询公司旗下联系人数量
     * @param $supplierId
     * @return int
     * @throws \yii\db\Exception
     */
    public function getLinkNumber($supplierId)
    {
        return (int)(new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier_contact_mapping')
            ->where(['supplier_id' => $supplierId])
            ->count('*', $this->dbConnection);
    }

    /**
     * 获取供应商服务分类字典表
     */
    public static function getSupplierBusinessDictionary()
    {
        $data = (new Query())->from('mycommunity_contractor_bigdata.yl_dim_basic_forest')
            ->select(['one_cate_name', 'one_cate_code', 'two_cate_name', 'two_cate_code', 'three_cate_name', 'three_cate_code'])
            ->all(\Yii::$app->get('dbSupplierBigdata'));
        $dictionary = [];
        if (!empty($data)) {
            foreach ($data as $business) {
                $dictionary[$business['three_cate_code']] = $business;
            }
        }
        return $dictionary;
    }

    /**
     * 通过公司名称返回供应商信息
     * @param string $companyName
     * @return mixed
     * @throws \yii\base\InvalidConfigException
     */
    public function getSupplierBasicInfoByCompanyName($companyName = '')
    {
        return (new Query())->from('mycommunity_contractor_bigdata.yl_s_supplier')
            ->select(['id', 'name'])
            ->where(['name' => $companyName])
            ->limit(1)
            ->one($this->dbConnection);
    }
}
